I have a google app script which (amongst other things) provides a series of drop downs in cells based on the values chosen in the previous cell. Essentially, when a cell value is selected, that value is used in a Google Big Query lookup, and the next cell is populated with values from Big Query.
I enforce the validation of the options available in the dropdown via the function below
function applyValidationToCell(list, cell) {
//create a valdation rule, essentially that the available values must be in the list passed ot the function
var rule = SpreadsheetApp.newDataValidation()
//.requireValueInList(list)
.requireValueInRange(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
}
This all worked well until I came across a case where there are over 500 options in the select list and I hit the error The data validation rule has more items than the limit of 500. Use the ‘List from a range’ criteria instead.
I’ve looked at the docs at https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder#requireValueInRange(Range) and also read this SO questions Need answer to “The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues” error and then @TheMaster's helpful answers at How do you resolve a "The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues" error and What does the range method getValues() return and setValues() accept?
When I was using .requireValueInList(list) the shape of the data passed to the function was ["Donruss","Leaf","Panini","Topps"]
To deal with requireValueInRange I’ve tried modifying the structure to 2D arrays as suggested in the answers above e.g. [["Donruss"],["Leaf"],["Panini"],["Topps"]] and [["Donruss","Leaf","Panini","Topps"]]
However, I always get the error Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.DataValidationBuilder.requireValueInRange.
Can anyone please advise on the correct way to pass values to requireValueInRange?
Description
Here is a simple example of how to use a range for a data validation rule.
In your case save the Big Query results to a sheet using setValues() then flush() then add the data validation rule for the cell your interested in.
Script
function runTest() {
try {
let spread = SpreadsheetApp.getActiveSpreadsheet();
let sheet = spread.getSheetByName("Sheet1");
let cell = sheet.getRange("A6");
let range = sheet.getRange("A1:A4");
let rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);
}
catch(err) {
console.log(err);
}
}
Reference